//
//  SinaHomeDAL.swift
//  MySinaWeiBoSwiftProject
//
//  Created by apple on 2016/11/26.
//  Copyright © 2016年 WangChen. All rights reserved.
//

/*
 CREATE TABLE IF NOT EXISTS t_statuses (statueId integer NOT NULL,statue text,userId text,createDate text DEFAULT (datetime('now','localtime')),PRIMARY KEY(statueId));
 */


import UIKit


private let expire_date_Intravel: Double = 5 * 60

class SinaHomeDAL: NSObject {
    
    
    // MARK:
    // MARK: - 将首页加载数据的方法迁移过来
    class func loadHomeData(sinceId: Int64, maxId: Int64, finish: @escaping ([[String : Any]]?)->()){
        //首先读取本地
        let result = checkoutStatuseFromCache(sinceId: sinceId, maxId: maxId)
        //有缓存
        if let res = result, res.count > 0{
            finish(res)
            return
        }
        
        //没有缓存
        //访问网络
        let paramter = ["access_token" : SinaUserAccountViewModel.shared.userAccount!.access_token!,
                        "max_id" : String(maxId),
                        "since_id" : String(sinceId)] as [String : Any]
        
        MyHTTPTools.sharedTool.request(requestMethod: .GET, urlStr: homeUrlStr, paramters: paramter) { (responserObject, error) in
            
            if error != nil {
                finish(nil)
            }
            
            //获取数据成功 将其转化为模型 使用yymodel
            let dict = responserObject as? [String:Any]
            
            guard let statuses = dict?["statuses"] as? [[String: Any]] else{
                finish(nil)
                return
            }
            
            //回调
            finish(statuses)
            //存入本地
            cacheStatus(array: statuses)
        }
    }
    
    
    
    
    
    // MARK:
    // MARK: - 若从网络获得文件 就把它存入数据库
    class func cacheStatus (array: [[String : Any]]){
        
        //先得到用户userId
        guard let userId = SinaUserAccountViewModel.shared.userAccount?.uid else {
            print("用户未登录..")
            return
        }
        
        let sql = "INSERT OR REPLACE INTO t_statuses (statueId,statue,userId) VALUES (?,?,?)"
        
        SinaSqliteTool.share.queue.inTransaction { (db, roolBack) in
            
            for item in array{
                
                let statueId = item["id"]!
                let statusData = try! JSONSerialization.data(withJSONObject: item, options: [])
                
                let res = db!.executeUpdate(sql, withArgumentsIn: [statueId,statusData,userId])
                
                if !res {
                    roolBack?.pointee = true
                    return
                }
            }
        }
    }
    
    
    // MARK:
    // MARK: - 读数据 检测本地是否有数据,从本地读出
    class func checkoutStatuseFromCache(sinceId: Int64,maxId: Int64) -> ([[String : Any]]?){
        
        guard let userId = SinaUserAccountViewModel.shared.userAccount?.uid else {
            print("用户未登录..")
            return nil
        }
        
        var sql = "SELECT statue FROM t_statuses WHERE userId = \(userId)"
        
        if sinceId > 0 {
            sql = sql + " AND statueId > \(sinceId)"
        }
        
        if maxId > 0 {
            sql = sql + " AND statueId < \(maxId)"
        }
        
        sql = sql + " ORDER BY statueId DESC"
        sql = sql + " limit 20"
        
        print(sql)
        
        var array = [[String : Any]]()
        SinaSqliteTool.share.queue.inDatabase { (db) in
            guard let res = db?.executeQuery(sql, withArgumentsIn: nil) else {
                return
            }
            
            while res.next() {
                let jsonData = res.data(forColumn: "statue")
                let dict = try! JSONSerialization.jsonObject(with: jsonData!, options: [])
                
                array.append(dict as! [String : Any])
            }
        }
        return array
    }
    
    
    
    
    // MARK:
    // MARK: - 消除过期数据
    class func clearCacheStatuse(){
        
        //过期时间
        let expireDate = Date(timeIntervalSinceNow: -expire_date_Intravel)
        
        let dateFormat = DateFormatter()
        dateFormat.locale = Locale(identifier: "en")
        
        dateFormat.dateFormat = "yyyy-MM-dd HH:mm:ss"
        
        let dateStr = dateFormat.string(from: expireDate)
        
        let sql = "DELETE FROM t_statuses WHERE createDate < ?"
        
        SinaSqliteTool.share.queue.inTransaction { (db, roolBack) in
            let res = db!.executeUpdate(sql, withArgumentsIn: [dateStr])
            if !res {
                roolBack?.pointee = true
            }
        }
        
    }
    
    
    
    
}
